package T03_QueryReadBlodTypeData;

import T00_Utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Collection;

/**
 * 查询数据表customers中Blob类型的字段
 *
 *
 */
public class QueryReadBlodTypeData {

      @Test
      public void  testQuery(){
          Connection conn=null;
          PreparedStatement ps=null;
          InputStream is=null;
          FileOutputStream outputStream=null;
          ResultSet rs=null;
          try {
              conn=JDBCUtils.getConnection();
               String sql = "SELECT id, name, email, birth, photo from customers WHERE id = ?";
               ps = conn.prepareStatement(sql);
               ps.setInt(1,21);
               //结果集
               rs=ps.executeQuery();
               if(rs.next()){
                   //方式一:
                   //int id=rs.getInt(1);
                   //String name = rs.getString(2);
                   //String email=rs.getString(3);
                   //Date date = rs.getDate(4);
                   //方式二:
                   int id = rs.getInt("id");
                   String name = rs.getString("name");
                   String email = rs.getString("email");
                   Date birth = rs.getDate("birth");
                   DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                   LocalDate date = LocalDate.parse(birth.toString(), df);
                   Customers customers=new Customers(id,name,email,date );
                   System.out.println(customers);

                   //将Blob类型的字段下载下来,以文件的方式保存在本地
                   Blob photo=rs.getBlob("photo");
                   is = photo.getBinaryStream();
                   outputStream=new FileOutputStream("zhangchengren.jpg");
                   byte [] buffer=new byte[1024];
                   int len; //每次按照1024 这个范围读取 只要不等于-1就是有东西
                   while ((len=is.read(buffer))!=-1){
                        outputStream.write(buffer,0,len);
                   }
               }
          } catch (Exception e) {
              e.printStackTrace();
          }finally {
                //资源关闭
              try {
                  if(is!=null){
                      is.close();
                  }

              } catch (IOException e) {
                  e.printStackTrace();
              }
              try {
                  if(is!=null){
                      outputStream.close();
                  }
              } catch (IOException e) {
                  e.printStackTrace();
              }
              JDBCUtils.closeResource(conn,ps);
          }

      }

}
